package com.woniuxy.mapper;

import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.CacheNamespace;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.FetchType;
import com.woniuxy.pojo.House;
import com.woniuxy.pojo.Student;
import com.woniuxy.pojo.User;
import com.woniuxy.provider.HouseProvider;

@CacheNamespace // 开启二级缓存
public interface HouseMapper {

	@Insert("INSERT INTO house VALUES (DEFAULT,#{address},#{max_number},#{occupy_number},#{house_rent},"
			+ "#{state},#{house_type},#{house_time},#{pay_type},#{house_sort},#{user_id})")
	public int add(House house);


	@Select("SELECT COUNT(*) FROM house")
	public int HouseCount();

	// 分页显示房间信息
	@Select("SELECT * FROM house LIMIT #{0},#{1}")
	@Results({ @Result(id = true, column = "house_id", property = "house_id"),
			@Result(column = "address", property = "address"), @Result(column = "max_number", property = "max_number"),
			@Result(column = "occupy_number", property = "occupy_number"),
			@Result(column = "house_rent", property = "house_rent"), @Result(column = "state", property = "state"),
			@Result(column = "house_type", property = "house_type"),
			@Result(column = "house_time", property = "house_time"),
			@Result(column = "pay_type", property = "pay_type"),
			@Result(column = "house_sort", property = "house_sort"), @Result(column = "user_id", property = "user_id"),
			@Result(column = "user_id", property = "user",

					one = @One(select = "findUserById", 

					fetchType = FetchType.LAZY
					
					))

	})
	public List<House> ShowAllByPage(int pageNumber, int pageSize);

	@Select("SELECT * FROM user WHERE user_number = #{id}")
	public User findUserById(int id);

	// 多条件查询房间
	@SelectProvider(type = HouseProvider.class, method = "select")
	@Results({ @Result(id = true, column = "house_id", property = "house_id"),
			@Result(column = "address", property = "address"), @Result(column = "max_number", property = "max_number"),
			@Result(column = "occupy_number", property = "occupy_number"),
			@Result(column = "house_rent", property = "house_rent"), @Result(column = "state", property = "state"),
			@Result(column = "house_type", property = "house_type"),
			@Result(column = "house_time", property = "house_time"),
			@Result(column = "pay_type", property = "pay_type"),
			@Result(column = "house_sort", property = "house_sort"), @Result(column = "user_id", property = "user_id"),
			@Result(column = "user_id", property = "user",

					one = @One(select = "findUserById",

							fetchType = FetchType.LAZY

					))

	})
	public List<House> select(Map<String, Object> map);

	// 查询符合条件的房间的数量
	@SelectProvider(type = HouseProvider.class, method = "selectCount")
	public int selectCount(House houser);

	// 删除房间
	@Delete("DELETE FROM house WHERE house_id = #{id}")
	public int delete(int house_id);

	// 查找房间已住人数
	@Select("SELECT occupy_number FROM house WHERE house_id = #{id}")
	public int findOccupyNumberById(int house_id);

	// 根据id查房间具体信息
	@Select("SELECT * FROM house WHERE house_id = #{house_id}")
	@Results({ @Result(id = true, column = "house_id", property = "house_id"),
			@Result(column = "address", property = "address"), @Result(column = "max_number", property = "max_number"),
			@Result(column = "occupy_number", property = "occupy_number"),
			@Result(column = "house_rent", property = "house_rent"), @Result(column = "state", property = "state"),
			@Result(column = "house_type", property = "house_type"),
			@Result(column = "house_time", property = "house_time"),
			@Result(column = "pay_type", property = "pay_type"),
			@Result(column = "house_sort", property = "house_sort"), @Result(column = "user_id", property = "user_id"),
			@Result(column = "user_id", property = "user",

					one = @One(select = "findUserById",
							// 设置懒加载
							fetchType = FetchType.LAZY

					))

	})
	public House findHouseById(int house_id);

	// 根据房间id查所有学生
	@Select("SELECT * FROM student WHERE house_id = #{id}")
	@Results({ @Result(id = true, column = "student_id", property = "student_id"),
			@Result(column = "student_name", property = "student_name"),
			@Result(column = "student_sex", property = "student_sex"),
			@Result(column = "grade_id", property = "grade_id"),
			@Result(column = "house_id", property = "house_id"),
			@Result(column = "user_number", property = "user_number"),
			@Result(column = "student_phone", property = "student_phone"),
			@Result(column = "grade_id", property = "grade_Name",

					one = @One(select = "findGradeNameById",
							// 设置懒加载
							fetchType = FetchType.LAZY

					))

	})
	public List<Student> findStudentByHouseId(int house_id);

	// 根据班级id查班级名称
	@Select("SELECT grade_Name FROM grade WHERE grade_id = #{id}")
	public String findGradeNameById(int grade_id);
	
	// 查询所有房间
	@Select("SELECT * FROM house")
	public List<House> all();
	

    // 入住-房间已住人数加一
    @Update("UPDATE house SET occupy_number = occupy_number+1 WHERE house_id = #{id}")
    public int inHouse(int house_id);

    // 退房
    @Update("UPDATE house SET occupy_number = occupy_number-1 WHERE house_id = #{id}")
    public int outHouse(int house_id);



}

